insert overwrite table jms_dm.dm_route_local_city partition(dt)
select * from jms_dm.dm_route_local_city_tmp1 where dt ='{{ execution_date | cst_ds }}'
union all
select * from jms_dm.dm_route_local_city_tmp2 where dt ='{{ execution_date | cst_ds }}';

insert into table jms_dm.dm_route_local_city partition (dt)
select
    in_warehouse.network_code as network_code,
    date_add('{{ execution_date | cst_ds }}',1) as update_date,
    in_whole.provider_id as in_from_provider_code,
    in_whole.city_id as in_from_city_code,
    in_whole.area_id as in_from_area_code,
    out_whole.provider_id as out_to_provider_code,
    out_whole.city_id as out_to_city_code,
    out_whole.area_id as out_to_area_code,
    out_whole.code as out_to_code,
    cast(rand()*100000000 as int) as in_branch_id,
    cast(rand()*100000000 as int) as main_id,
    cast(rand()*100000000 as int) as out_branch_id,
    '' as in_collect_code,
    '' as in_to_code,
    '' as start_center_code,
    cast(in_warehouse.span_days as int)+if(in_warehouse.latest_arrival_time<out_warehouse.latest_arrival_time,0,1)+cast(out_warehouse.span_days as int) as search_type,
    in_warehouse.network_code as start_network_code,
    in_whole.regional_id as in_from_regional_code,
    in_whole.regional_desc as in_from_regional_desc,
    in_whole.financial_center_code as in_from_financial_center_code,
    in_whole.financial_center_desc as in_from_financial_center_desc,
    in_whole.provider_desc as in_from_provider_desc,
    in_whole.city_desc as in_from_city_desc,
    in_whole.area_desc as in_from_area_desc,
    '' as in_collect_name,
    in_warehouse.network_name as in_from_name,
    '' as in_to_name,
    in_warehouse.latest_arrival_time as in_edge_latest_warehousing,
    '' as in_edge_planned_departure,
    '' as in_edge_planned_arrival,
    '' as in_edge2_planned_departure,
    '' as in_edge2_planned_arrival,
    '' as branch_in_span_days, --不确定
    '' as branch_in_total_time,  --不确定
    1 as is_main_route,
    0 as transfer_type,
    0 as num_of_transfer,
    '' as whole_route,
    '' as e1_center_flow,
    '' as e1_line_code,
    '' as e1_planned_departure,
    '' as e1_runtime,
    '' as e1_planned_arrival,
    '' as e1_e2_stop_time,
    '' as e2_center_flow,
    '' as e2_line_code,
    '' as e2_planned_departure,
    '' as e2_runtime,
    '' as e2_planned_arrival,
    '' as e2_e3_stop_time,
    '' as e3_center_flow,
    '' as e3_line_code,
    '' as e3_planned_departure,
    '' as e3_runtime,
    '' as e3_planned_arrival,
    '' as e3_e4_stop_time,
    '' as e4_center_flow,
    '' as e4_line_code,
    '' as e4_planned_departure,
    '' as e4_runtime,
    '' as e4_planned_arrival,
    '' as e4_e5_stop_time,
    '' as e5_center_flow,
    '' as e5_line_code,
    '' as e5_planned_departure,
    '' as e5_runtime,
    '' as e5_planned_arrival,
    '' as e5_e6_stop_time,
    '' as e6_center_flow,
    '' as e6_line_code,
    '' as e6_planned_departure,
    '' as e6_runtime,
    '' as e6_planned_arrival,
    '' as e6_e7_stop_time,
    '' as e1_end_code,
    '' as e2_end_code,
    '' as e3_end_code,
    '' as e4_end_code,
    '' as e5_end_code,
    '' as e6_end_code,
    '' as transfer_total_span_days,
    '' as transfer_total_time_use,
    '' as main_total_span_days,
    '' as main_total_time_use,
    '' as end_center,
    '' as end_center_code,
    '' as start_center,
    '' as out_from_code,
    '' as out_collect_code,
    out_whole.regional_desc as out_to_regional_desc,
    out_whole.regional_id as out_to_regional_code,
    out_whole.financial_center_desc as out_to_financial_center_desc,
    out_whole.financial_center_code as out_to_financial_center_code,
    out_whole.provider_desc as out_to_provider_desc,
    out_whole.city_desc as out_to_city_desc,
    out_whole.area_desc as out_to_area_desc,
    '' as out_collect_name,
    out_warehouse.network_name as out_to_name,
    '' as out_from_name,
    0 as out_has_collect,
    '' as out_edge_planned_departure,
    '' as out_edge_planned_arrival_time,
    '' as out_edge2_planned_departure,
    '' as out_edge2_planned_arrival_time,
    '' as out_edge2_latest_warehouse_time,
    '' as out_edge_latest_warehouse_time,
    '' as out_edge_span_days_sign,
    '' as out_edge2_span_days_sign,
    '23:59:59' as out_edge_deadline_sign_time	,
    '' as out_edge2_deadline_sign_time,
    out_warehouse.span_days as branch_out_span_days, --不确定
    (unix_timestamp('23:59:59','HH:mm:ss')- unix_timestamp(out_warehouse.latest_arrival_time,'HH:mm'))/60 branch_out_total_time, --不确定
    in_warehouse.start_time as effective_date,
    in_warehouse.end_time as expiration_date,
    '' as branch_in_center_stop_time,
    '' as center_branch_out_stop_time,
    '' as branch_in_center_span_days,
    '' as center_branch_out_span_days,
    0 as is_circuitous,
    '' as out_edge_span_days_arrive,
    '' as out_edge2_span_days_arrive,
    1 as in_nodes,
    1 as out_nodes,
    case WHEN cast(in_warehouse.span_days as int)+if(in_warehouse.latest_arrival_time<out_warehouse.latest_arrival_time,0,1)+cast(out_warehouse.span_days as int)<1 then '当日达'
         when cast(in_warehouse.span_days as int)+if(in_warehouse.latest_arrival_time<out_warehouse.latest_arrival_time,0,1)+cast(out_warehouse.span_days as int)<2 then '次日达'
         when cast(in_warehouse.span_days as int)+if(in_warehouse.latest_arrival_time<out_warehouse.latest_arrival_time,0,1)+cast(out_warehouse.span_days as int)<3 then '隔日达'
         when cast(in_warehouse.span_days as int)+if(in_warehouse.latest_arrival_time<out_warehouse.latest_arrival_time,0,1)+cast(out_warehouse.span_days as int)<4 then '三日达'
         when cast(in_warehouse.span_days as int)+if(in_warehouse.latest_arrival_time<out_warehouse.latest_arrival_time,0,1)+cast(out_warehouse.span_days as int)<5 then '四日达'
         when cast(in_warehouse.span_days as int)+if(in_warehouse.latest_arrival_time<out_warehouse.latest_arrival_time,0,1)+cast(out_warehouse.span_days as int)<6 then '五日达'
         else '五日达以上' end as total_days_use,
    (unix_timestamp('23:59:59','HH:mm:ss')- unix_timestamp(in_warehouse.latest_arrival_time,'HH:mm'))/60+
    (cast(in_warehouse.span_days as int)+if(in_warehouse.latest_arrival_time<out_warehouse.latest_arrival_time,0,1)+cast(out_warehouse.span_days as int))*24*60 as total_time_use,
    concat('T+',cast(in_warehouse.span_days as int)+if(in_warehouse.latest_arrival_time<out_warehouse.latest_arrival_time,0,1)+cast(out_warehouse.span_days as int)) as total_days_t,
    1 as total_nodes,
    in_warehouse.apply_day as working_days,
    '' as extra_in_collect_code,
    '' as extra_in_collect_name,
    '' as extra_in_line_name,
    '' as extra_in_latest_warehousing,
    '' as extra_in_planned_departure,
    '' as extra_in_planned_arrival,
    '' as extra_out_collect_code,
    '' as extra_out_collect_name,
    '' as extra_out_line_name,
    '' as extra_out_latest_warehousing,
    '' as extra_out_planned_departure,
    '' as extra_out_planned_arrival,
    '' as in_network_line_name,
    '' as in_collect_line_name,
    '' as out_network_line_name,
    '' as out_collect_line_name,
    '' as town_plus_time,
    '' as branch_in_ship_time,
    '' as branch_in_ship_span_days,
    '' as branch_out_ship_time,
    '' as branch_out_ship_span_days,
    '' as all_line_name,
    '' as out_town_plus_time,
    '' as in_edge_run_time,
    '' as in_edge_span_days,
    '' as in_edge2_run_time,
    '' as in_edge2_span_days,
    '' as in_edge3_run_time,
    '' as in_edge3_span_days,
    '' as out_edge_run_time,
    '' as out_edge_span_days,
    '' as out_edge2_run_time,
    '' as out_edge2_span_days,
    '' as out_edge3_run_time,
    '' as out_edge3_span_days,
    '' as e1_span_days,
    '' as e2_span_days,
    '' as e3_span_days,
    '' as e4_span_days,
    '' as e5_span_days,
    '' as e6_span_days,
    cast((unix_timestamp('23:59:59','HH:mm:ss')- unix_timestamp(out_warehouse.latest_arrival_time,'HH:mm'))/60 as int)  as delivery_time, --派件耗时,目的网点到车时间与签收时间的总耗时
    concat_ws('-',in_warehouse.network_name,out_warehouse.network_name) as route_flow, --流向
    '' as in_edge2_latest_warehousing,
    '' as e1_latest_warehousing,
    '' as e2_latest_warehousing,
    '' as e3_latest_warehousing,
    '' as e4_latest_arrival_time,
    '' as e5_latest_arrival_time,
    '' as e6_latest_arrival_time,
    '' as start_taking_shift,
    '' as end_send_shift,
    out_warehouse.latest_arrival_time  as warehouse_end_time, --规划出仓时间
    in_warehouse.span_days  as reserve_1,
    '' as reserve_2,
    0 as route_contain_main_line,
    '' as direct_route_type,
    if(in_warehouse.latest_arrival_time<out_warehouse.latest_arrival_time,0,1) as in_line_day, --收件班次-支线衔接跨天
    '' as out_line_day, --派件班次-支线衔接跨天
    '' as in_ship_span_days,
    in_whole.manage_code as in_manage_region_code,
    in_whole.manage_name as in_manage_region_name,
    out_whole.manage_code as out_manage_region_code,
    out_whole.manage_name as out_manage_region_name,
    concat_ws('-',receipt_start_time,receipt_end_time) as latest_warehousing_time_quantum,
    null as in_edge_operate_time,
    null as in_edge2_operate_time,
    null as extra_in_operate_time,
    null as out_edge_operate_time,
    null as extra_out_operate_time,
    null as out_edge2_operate_time,
    case when SUBSTRING(in_warehouse.shift_name,-3)='01P'then 1
         when SUBSTRING(in_warehouse.shift_name,-3)='02P'then 2
         else null end  as shift_code,
    null,
    null,
    case when SUBSTRING(out_warehouse.shift_name,-3)='01D' then 1
         when SUBSTRING(out_warehouse.shift_name,-3)='02D' then 2
         when SUBSTRING(out_warehouse.shift_name,-3)='03D' then 3 else null end  as shift_code,
    null,
    null,
    '{{ execution_date | cst_ds }}' as dt
from
    (select * from jms_dim.dim_yl_tmsnew_tms_in_warehouse_shi_base_dt where  dt ='{{ execution_date | cst_ds }}' and STATUS = 1 and IS_DELETE = 1 and IS_ENABLE=1) in_warehouse
        join (select * from jms_dim.dim_yl_tmsnew_tms_out_warehouse_sh_base_dt  where  dt ='{{ execution_date | cst_ds }}' and STATUS = 1 and IS_DELETE = 1 and IS_ENABLE=1)  out_warehouse
            on out_warehouse.network_code = in_warehouse.network_code
        left join jms_dim.dim_network_whole_massage in_whole on in_whole.code = in_warehouse.network_code
        left join jms_dim.dim_network_whole_massage out_whole on out_warehouse.network_code = out_whole.code;